<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Object extends CI_Model {

    private $CI;
    
    function __construct() {
        parent::__construct();
    }
    
    function get_all(){
    	return $this->db->get('object')->result();
    }
    
    function get($obj_id) {
    	$result = $this->db->where('id',$obj_id)->get('object');
		if ($result->num_rows() == 0)
			throw new Exception("Object #$obj_id not found");
		return $result->row();
    	
    }
    
    private function exists($name) {
    	if ($this->db->where('table',$name)->get('object')->num_rows()>0)
    		return $this->db->query('SHOW TABLES LIKE \''.URBAN_TABLE_PREFIX.$name."'")->num_rows()>0;
    	return FALSE;
    }
    
    function insert($name) {
    	$name = mysql_real_escape_string($name);
    	
    	if ($this->exists($name)) 
    		throw new Exception("Object $name already exists");
    	
    	$this->db->query('CREATE TABLE '.URBAN_TABLE_PREFIX."$name (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    		created_on TIMESTAMP DEFAULT '0000-00-00 00:00:00', 
			modified_on TIMESTAMP DEFAULT NOW() ON UPDATE NOW(), 
			created_by INT, 
			modified_by INT, 
			published TINYINT DEFAULT 0,
			category INT DEFAULT NULL,
			CONSTRAINT `fk_cr`
				FOREIGN KEY (`created_by`)
				REFERENCES `user`.`id`
				ON DELETE SET NULL
				ON UPDATE CASCADE,
			CONSTRAINT `fk_mo`
				FOREIGN KEY (`modified_by`)
				REFERENCES `user`.`id`
				ON DELETE SET NULL
				ON UPDATE CASCADE,
			CONSTRAINT `fk_cat`
				FOREIGN KEY(`category`)
				REFERENCES `category`.`id`
				ON DELETE SET NULL
				ON UPDATE CASCADE)
    	");

    	if ($this->db->_error_number()!=0)
			throw new Exception('Error '.$this->db->_error_number().': '.$this->db->_error_message());

    	$this->db->insert('object',array(
    		'table'=>$name,
    	));
		if ($this->db->_error_number()!=0)
			throw new Exception('Error: '.$this->db->_error_number().': '.$this->db->_error_message());
    		
		if ($this->db->affected_rows() == 0)
			throw new Exception('Object not inserted');
    	    	
    }

    function update($object_id,$name) {
    	$name = mysql_real_escape_string($name);

    	//check if new name exists
    	if ($this->exists($name)) 
    		throw new Exception("Object $name already exists");

    	//rename table
    	$this->db->query('RENAME TABLE '.URBAN_TABLE_PREFIX.$this->get($object_id)->table.' TO '.URBAN_TABLE_PREFIX.$name);

    	if ($this->db->_error_number()!=0)
			throw new Exception('Error '.$this->db->_error_number().': '.$this->db->_error_message());

		//rename table metadata
		$this->db->where('id',$object_id)->update('object',array(
    		'table'=>$name
    	));

    	if ($this->db->affected_rows() == 0)
			throw new Exception("Object #$object_id not found");

    }

    function delete($object_id) {
    	$this->db->query('DROP TABLE '.URBAN_TABLE_PREFIX.$this->get($object_id)->table);

    	if ($this->db->_error_number()!=0)
			throw new Exception('Error '.$this->db->_error_number().': '.$this->db->_error_message());

    	$this->db->where('id',$object_id)->delete('object');
    	
    	if ($this->db->affected_rows() == 0)
			throw new Exception("Object #$object_id not found");
    	
    }
    
    function get_entries($object_id) {
   		$object_name = $this->object->get($object_id)->table;
   		
    	return $this
    		->db
    		->get(URBAN_TABLE_PREFIX.$object_name)
    		->result();
    }
   
    function get_entry($object_id,$row_id,$media=FALSE) {
   		$object_name = $this->object->get($object_id)->table;
   		
    	$result = $this
    		->db
    		->query(
    			"SELECT ".
    				URBAN_TABLE_PREFIX."$object_name.*,
		    		(SELECT GROUP_CONCAT(tag.name) FROM tag,entry_has_tag
						WHERE
						 entry_has_tag.row_id = ".URBAN_TABLE_PREFIX."$object_name.id && 
						 entry_has_tag.object_id = $object_id &&
						 entry_has_tag.tag_id = tag.id
					) AS tags,
					creator.email AS created_by_email,
					modifier.email AS modified_by_email
				FROM ". URBAN_TABLE_PREFIX.$object_name.
    			" LEFT JOIN user AS creator ON created_by=creator.id
    			LEFT JOIN user AS modifier ON modified_by=modifier.id 
    			WHERE ".URBAN_TABLE_PREFIX."$object_name.id = $row_id")
    		->row();
    	
    	if ($media) {
    		$this->load->model('media');
    		$result->media = $this->media->get($object_id,$row_id);
    	};

    	return $result;
    }

    function set_published($object_id,$row_id,$published) {
   		$object_name = $this->object->get($object_id)->table;
   		
   		$this
   			->db
   			->where('id',$row_id)
   			->update(URBAN_TABLE_PREFIX.$object_name,array(
   				'published'=>$published ? 1 : 0
   			));
    }
    
   	function delete_data($object_id,$row_id) {
   		$object_name = $this->object->get($object_id)->table;
    	
   		$this->db->where('id',$row_id)->delete(URBAN_TABLE_PREFIX.$object_name);
   		
    	if ($this->db->_error_number()!=0)
			throw new Exception('Error '.$this->db->_error_number().': '.$this->db->_error_message());

    	if ($this->db->affected_rows() == 0)
			throw new Exception("Data #$row_id not found in object $object_name");
    }
    
    function insert_data($object_id,$data) {
   		$object_name = $this->object->get($object_id)->table;
   		
   		foreach ($data as $id=>$datum)
   			$data[$id] = mysql_real_escape_string($datum);
    	
   		$data['created_by'] = $data['modified_by'] = $this->user->get_current()->id;
   		$data['created_on'] = $data['modified_on'] = NULL; //http://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0 
   		
   		$this->db->insert(URBAN_TABLE_PREFIX.$object_name,$data);
   		
    	if ($this->db->_error_number()!=0)
			throw new Exception('Error '.$this->db->_error_number().': '.$this->db->_error_message());

    	if ($this->db->affected_rows() == 0)
			throw new Exception("Data was not inserted into object $object_name");

		return $this->db->insert_id();
    }

    function update_data($object_id,$row_id,$data) {
   		$object_name = $this->object->get($object_id)->table;
   		
   		foreach ($data as $id=>$datum)
   			$data[$id] = mysql_real_escape_string($datum);

   		$data['modified_by'] = $this->user->get_current()->id;
    	
   		$this->db->where('id',$row_id)->update(URBAN_TABLE_PREFIX.$object_name,$data);

   		if ($this->db->affected_rows() == 0)
			throw new Exception("Data #$row_id not found in object $object_name");
   		
    }
    
    function set_tags($object_id,$row_id,$tags) {
    	$aTagsQuoteParen = array();
    	$aTags = array();
    	$aTagsQuote = array();
    	foreach (array_unique(explode(',',$tags)) as $tag) {
    		$tag = mysql_real_escape_string(substr(trim($tag),0,32));
    		if (strlen($tag)>0) {
    			$aTags[] = $tag;
    			$aTagsQuote[] = "'$tag'";
    			$aTagsQuoteParen[] = "('$tag')";
    		}
    	};

    	$this
    		->db
    		->where('object_id',$object_id)
    		->where('row_id',$row_id)
    		->delete('entry_has_tag');
    	
    	$this->db->query('INSERT IGNORE INTO tag(name) VALUES'.implode(',',$aTagsQuoteParen));
    	
    	$result_ids = $this->db->query('SELECT id FROM tag WHERE LOWER(name) IN ('.strtolower(implode(',',$aTagsQuote).')'))->result();
    	$tag_relations = array();
    	foreach($result_ids as $result_id)
    		$tag_relations[] = "($object_id,$row_id,$result_id->id)";
    	
    	$this
    		->db
    		->query('INSERT INTO entry_has_tag(object_id,row_id,tag_id) VALUES'.implode(',',$tag_relations));
    		
    }
    
    function set_cat($object_id, $row_id, $cat_id) {
   		$object_name = $this->object->get($object_id)->table;
   		
    	$this
    		->db
    		->where('id',$row_id)
    		->update(URBAN_TABLE_PREFIX.$object_name,array(
    			'category'=>$cat_id
    		));
    	
    	if ($this->db->_error_number()!=0)
			throw new Exception('Error '.$this->db->_error_number().': '.$this->db->_error_message());

    	if ($this->db->affected_rows() == 0)
			throw new Exception("Category #$cat_id was not set to $object_name entry #$row_id");
    }
    
}?>